<?php
class M_levels extends MY_Model {
	
	private $__table = 'levels';
	private $last_insert_id = NULL;
	
	public function get_all_levels($id=FALSE)
	{
		if($id == FALSE):
			if($this->c_e_gl != '' && !empty($this->c_e_gl)):
				$levels = explode( ',', $this->c_e_gl );
				$query = $this->db->where_in('level_id',$levels)->get($this->__table);
				return $query->num_rows() >= 1 ? $query->result() : FALSE;
			else:
				$query = $this->db->get($this->__table);
				return $query->num_rows() >= 1 ? $query->result() : FALSE;
			endif;
		else:
			$query = $this->db->where('level_id',$id)->get($this->__table);
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		endif;
	}
	
	public function get_all_levels_array($choose = TRUE)
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
			$levels = explode( ',', $this->c_e_gl );
			$query = $this->db->select(array('level_id','level_desc','level_code'))->where_in('level_id',$levels)->get($this->__table);
			if($query->num_rows() > 0):
				if($choose == TRUE):
					$result[''] ='-- Choose A Level --';
				endif;
				foreach($query->result() as $q):
					$result[$q->level_id] = $q->level_code.' | '.$q->level_desc;
				endforeach;
				return $result;
			else:
				return FALSE;
			endif;
		else:
			$query = $this->db->select(array('level_id','level_desc','level_code'))->get($this->__table);
			if($query->num_rows() > 0):
				if($choose == TRUE):
					$result[''] ='-- Choose A Level --';
				endif;
				foreach($query->result() as $q):
					$result[$q->level_id] = $q->level_code.' | '.$q->level_desc;
				endforeach;
				return $result;
			else:
				return FALSE;
			endif;
		endif;
	}
	
	public function get_all_levels_array_two($choose = TRUE)
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
			$levels = explode( ',', $this->c_e_gl );
			$query = $this->db->select(array('level_id','level_desc','level_code'))->where_in('level_id',$levels)->get($this->__table);
			if($query->num_rows() > 0):
				foreach($query->result() as $q):
					$result[$q->level_id] = $q->level_code.' | '.$q->level_desc;
				endforeach;
				return $result;
			else:
				return FALSE;
			endif;
		else:
			$query = $this->db->select(array('level_id','level_desc','level_code'))->get($this->__table);
			if($query->num_rows() > 0):
				foreach($query->result() as $q):
					$result[$q->level_id] = $q->level_code.' | '.$q->level_desc;
				endforeach;
				return $result;
			else:
				return FALSE;
			endif;
		endif;
	}
	
	/*
		with vp restrictions
	*/
	public function get_all_vp_levels_array($choose = TRUE)
	{	
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
			$levels = explode( ',', $this->c_e_gl );			
			$query_for_none = $this->db->select(array('level_id','level_desc','level_code'))->where_in('level_id',$levels)->get($this->__table);
			if($query_for_none->num_rows() > 0):
				if($choose == TRUE):
					$result[''] ='-- Choose A Level --';
				endif;
				foreach($query_for_none->result() as $q):
					$result[$q->level_id] = $q->level_code.' | '.$q->level_desc;
				endforeach;
				return $result;
			else:
				return FALSE;
			endif;
		else:
			$query_for_none = $this->db->select(array('level_id','level_desc','level_code'))->get($this->__table);
			if($query_for_none->num_rows() > 0):
				if($choose == TRUE):
					$result[''] ='-- Choose A Level --';
				endif;
				foreach($query_for_none->result() as $q):
					$result[$q->level_id] = $q->level_code.' | '.$q->level_desc;
				endforeach;
				return $result;
			else:
				return FALSE;
			endif;
		endif;
		
		/*$query = $this->db->select(array('level_id','level_desc','level_code'))->get($this->__table);
		if($query->num_rows() > 0)
		{
			if($choose == TRUE){
				$result[''] ='-- Choose A Level --';
			}
			foreach($query->result() as $q)
			{
				$result[$q->level_id] = $q->level_code.' | '.$q->level_desc;
			}
			return $result;
		}else{
			return FALSE;
		}*/
	}
	
	/*
		query to count all grade level for pagnation
		tomex
		11/12/11/2012
	*/	
	public function count_grade_level()
	{
		return $this->db->count_all($this->__table);
	}
	
	/*
		query to fetch all grade level for pagnation
		tomex
		11/12/11/2012
	*/	
	public function fetch_grade_level($limit, $start)
	{
        $this->db->limit($limit, $start);
        $query = $this->db->select(array('level_id','level_desc','level_stat','level_code'))->get($this->__table);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}

	/*
		query to update level
		tomex
		11/12/11/2012
	*/
	public function update_grade_level($input)
	{
		$this->db->update_batch($this->__table,$input,'level_id');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	/*
		add_grade_level
		@param array
		adds grade level to database
		tomex
		12/11/2012
	*/
	public function add_grade_level($input)
	{
		if(is_array($input))
		{
			$input = array_map('strtolower',$input);
			
			$get_grade_level = $this->db->or_where('level_code',$input['level_code'])
										->or_where('level_desc',$input['level_desc'])
										->get($this->__table);
			if($get_grade_level->num_rows() >= 1)
			{
				return (object)array('status'=>'exists','message'=>'A Grade Level with this data already exists.');
			}else
			{
				$input['level_created'] = NOW;
				$this->db->insert($this->__table,$input);
				$TRUE = (object)array('status'=>'true','message'=>'Grade Level Was Successfully Created');
				$FALSE = (object)array('status'=>'false','message'=>'Grade Level Was Not Created.');
				
				return $this->db->affected_rows() >= 1 ? $TRUE : $FALSE;

			}
		}

	}
	
	/*
		query to update level_stat to inactive
		tomex
		11/12/11/2012
	*/
	public function destroy($id)
	{
		$query = $this->db->select('count(e_id) as total')
						  ->where('e_grade_level',$id)
						  ->where('e_ay_id',$this->c_sy->id)
						  ->get('enrollments');
						  
		if($query->row()->total >= 1)
		{
			return (object)array('status'=> FALSE,'log'=>'Cannot Delete Grade Level, Students are already Enrolled in this Grade Level');
		}else{
			$this->db->where('level_id',$id)->delete($this->__table);
			$true = array('status'=>TRUE,'log'=>'Successfully Deleted Grade Level');
			$false = array('status'=>FALSE,'log'=>'An error was encountered while trying to delete Grade Level, Try Again.');
			
			return $this->db->affected_rows() > 0 ? (object)$true : (object)$false;
		}
	}
	
	
	/* verify_data
	 * @param array
	 * @param int
	*  verifies if data entered by user is existing already in the database
	*  $strict level 1,2
	*  level 1 = if one field has existing data in database returns true else false
	*  level 2 = if all field has existing data in database returns true else false
	*  12/11/2012
	*/
	public function verify_data($data,$strict_level)
	{		
			if($strict_level == 1)
			{
				$query = $this->db->or_where($data)->get($this->__table);
			}elseif($strict_level == 2){
				$query = $this->db->where($data)->get($this->__table);
			}
			
			return $query->num_rows() > 0 ? TRUE : FALSE;
	}
	
	
	public function get_grade_level($level_id){
		$q = $this->db->where('level_id', $level_id)->get('levels');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function get_specific_coloumn($data)
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
			$levels = explode( ',', $this->c_e_gl );
			$query = $this->db->select($data)->where_in('level_id',$levels)->get($this->__table);
			return $query->num_rows() >= 1 ? $query->result_array() : FALSE;
		else:
			$query = $this->db->select($data)->get($this->__table);
			return $query->num_rows() >= 1 ? $query->result_array() : FALSE;
		endif;
	}
	
	public function create_heiarchy_options()
	{
		$used_numbers = $this->db->query('SELECT level_hierarchy FROM levels')->result_array();
		$total_count = $this->db->query('SELECT count("level_id") as total FROM levels')->row()->total;
		$options = range(0,intval($total_count));
		
		
		if(!$total_count):
			return array('no');
		else:
			return $options;
			array_unshift($options, "phoney");
			unset($options[0]);
		endif;
	}
	
	public function last_insert_id()
	{
		return $this->last_insert_id;
	}
	
	public function delete_grade_level($id)
	{
		$this->db->where('level_id',$id)->limit(1)->delete('levels');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_levels_for_promotions($level = '')
	{
		if(in_array($level,['preschool','primary','gradeschool','elementary']))
		{
			$sql = 'SELECT level_id,level_code,level_desc
					FROM levels
					WHERE level_category IN ("preschool","primary","gradeschool")
					AND level_stat = "active"';
		
		}elseif(in_array($level,['intermediate','highschool']))
		{
			$sql = 'SELECT level_id,level_code,level_desc
					FROM levels
					WHERE level_category IN ("intermediate","highschool")
					AND level_stat = "active"';
		}else{
			
			$sql = FALSE;
		}
		
		if($sql !== FALSE)
		{
			$query = $this->db->query($sql);
			return $query->num_rows() >=1 ? $query->result() : FALSE;
		}else{
			return FALSE;
		}
	}
	
	public function get_all_levels_for()
	{
		$result = $this->db->select('level_id,level_desc')->where('level_stat','active')->get('levels');
		return $result->num_rows() >= 1 ? $result->result() : FALSE;
	}
	
	public function get_sections_and_students_and_block($level_id)
	{
		$sql = 'SELECT sections.id,
					   section_name,
					   level_desc,block_name,
					   concat_ws("",emp.last_name," , ",emp.first_name," ",emp.middle_name) as employee,
					   bs.block_name
				FROM sections
				LEFT JOIN levels on levels.level_id = sections.grade_level_id
				LEFT JOIN block_section bs on bs.id = sections.block_id
				LEFT JOIN employees emp on emp.emp_id = sections.adviser_id
				WHERE grade_level_id = ?
				AND section_status = "active"';

		$q = $this->db->query($sql,[$level_id]);

		if($q->num_rows() >= 1 )
		{
			foreach($q->result() as $k => $v)
			{

				$section['gradelevel'] = ucwords(strtolower($v->level_desc));
				$section['section'][$k]['sname'] = ucwords(strtolower($v->section_name));
				$section['section'][$k]['bname'] = ucwords(strtolower($v->block_name));
				$section['section'][$k]['adviser'] = ucwords(strtolower($v->employee));
				$section['section'][$k]['male'] = $this->get_section_students($v->id,'male');
				$section['section'][$k]['female'] = $this->get_section_students($v->id,'female');
			}
			
			$section['usection']['male'] = $this->get_section_students(FALSE,'male',$level_id);
			$section['usection']['female'] = $this->get_section_students(FALSE,'female',$level_id);

			return $section;
		}
	}
	
	private function get_section_students($section_id,$gender= '',$level = FALSE)
	{
		if($section_id !== FALSE)
		{
			$sql = 'SELECT concat_ws("",p.lastname," , ",p.firstname," ",p.middlename) as fullname,
						   p.dob,
						   block_name
					FROM enrollments e
					LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
					LEFT JOIN block_section bs ON bs.id = e.block
					WHERE e.e_section_id = ?
					AND p.gender = ?
					AND e.e_ay_id = ?
					ORDER BY p.lastname';
			$q = $this->db->query($sql,[$section_id,$gender,$this->c_sy->id]);
		}elseif($section_id == FALSE && $level !== FALSE)
		{
			$sql = 'SELECT concat_ws("",p.lastname," , ",p.firstname," ",p.middlename) as fullname,
						   p.dob,
						   block_name
					FROM enrollments e
					LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
					LEFT JOIN block_section bs ON bs.id = e.block
					WHERE p.gender = ?
					AND registrar_approved = 1
					AND e.e_section_id = 0
					AND verified = 1
					AND e.e_ay_id = ?
					AND e.e_grade_level = ?
					ORDER BY p.lastname';
			$q = $this->db->query($sql,[$gender,$this->c_sy->id,$level]);
		}

		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_lesser_level($lid=FALSE)
	{
		if($lid)
		{
			$query = $this->db->select(array('level_hierarchy'))->where('level_id', $lid)->get($this->__table);
			$rs = $query->row();

			$query = $this->db->select(array('level_id','level_desc','level_code'))->where('level_hierarchy >', $rs->level_hierarchy)->get($this->__table);
			return $query->num_rows() > 0 ? $query->row() : FALSE;
		}else{
			$this->get_all_levels_array();
		}
	}
}
?>